package com.zxy.supplier_system.server.entity

import com.zxy.supplier_system.server.repository.GoodsSummaryWithPrice
import com.zxy.supplier_system.server.repository.GoodsSummaryWithRememberedPurchasePrice
import com.zxy.supplier_system.server.utils.GoodsRow
import jakarta.persistence.*
import org.hibernate.annotations.ColumnDefault
import org.hibernate.annotations.DynamicInsert
import org.hibernate.annotations.DynamicUpdate
import org.springframework.data.annotation.CreatedDate
import org.springframework.data.annotation.LastModifiedDate
import org.springframework.data.jpa.domain.support.AuditingEntityListener
import java.math.BigDecimal
import java.time.OffsetDateTime
import java.time.Period

/**
 * 货品
 */
@Entity
@EntityListeners(AuditingEntityListener::class)
@Table(
    uniqueConstraints = [
        UniqueConstraint(
            name = "goods_barcode_system_service_id_uindex",
            columnNames = ["barcode", "system_service_id"]
        )
    ]
)
@DynamicUpdate
@DynamicInsert
@SqlResultSetMapping(
    name = "GoodsSummaryWithPrice",
    classes = [
        ConstructorResult(
            columns = [
                ColumnResult(name = "id"),
                ColumnResult(name = "name"),
                ColumnResult(name = "barcode"),
                ColumnResult(name = "price"),
                ColumnResult(name = "rememberedPrice"),
            ],
            targetClass = GoodsSummaryWithPrice::class
        )
    ],
)
@NamedNativeQuery(
    query = """
        select g.id, g.name, g.barcode,g.price,dsoi.price as rememberedPrice
from goods g
         left join `supplier-system`.direct_sale_order_item dsoi on g.id = dsoi.goods_id
         left join `supplier-system`.direct_sale_order dso on dsoi.order_id = dso.id
         left join (select g.id as id, max(dso.id) as oid
                     from goods g
                              left join `supplier-system`.direct_sale_order_item dsoi on g.id = dsoi.goods_id
                              left join `supplier-system`.direct_sale_order dso on dsoi.order_id = dso.id
                              where dso.client_id = ?1 
                     group by g.id) i
                    on i.id = g.id
where i.oid = dso.id or dso.id is null
        """, name = "Goods.findAllWithRememberedDirectSalePrice", resultSetMapping = "GoodsSummaryWithPrice"
)
@SqlResultSetMapping(
    name = "GoodsSummaryWithRememberedPurchasePrice",
    classes = [
        ConstructorResult(
            columns = [
                ColumnResult(name = "id"),
                ColumnResult(name = "name"),
                ColumnResult(name = "barcode"),
                ColumnResult(name = "price"),
                ColumnResult(name = "rememberedPurchasePrice"),
                ColumnResult(name = "rememberedUnitId"),
                ColumnResult(name = "rememberedAmount"),
            ],
            targetClass = GoodsSummaryWithRememberedPurchasePrice::class
        )
    ],
)
@NamedQuery(
    query = """
        select new com.zxy.supplier_system.server.repository.GoodsSummaryWithRememberedPurchasePrice(g,sg, p.purchasePrice, p.unitId, p.amount) from Goods g
        left join g.subGoodsList sg
left join (select ig.id as id ,isg.id as sid, max (c.id) as oid from Goods as ig
left join ig.subGoodsList isg
left join ig.procurementItems c 
left join c.procurement p 
left join p.supplier s where (isg.id = c.subGoods.id or (isg.id is null and c.subGoods.id is null )) and s.id = ?1
 group by ig.id,isg.id) as i on i.id = g.id and (sg.id = i.sid or (sg.id is null and i.sid is null ))
 left join (select id as id, purchasePrice as purchasePrice ,unit.id as unitId, amount as amount from ProcurementItem pi) as p on p.id = i.oid
    """,
    name = "Goods.findAllWithRememberedProcurementPurchasePrice",
)
@NamedNativeQuery(
    query = """
    select g.*, sg.id as sg_id,sg.name as sg_name , sg.number as sg_number,sg.goods_id,sg.amount as sg_amount, coalesce((select pr.price from procurement_return_item pr 
    left join (select ig.id as id,s.id as sid ,max(pi.id) as oid from goods as ig 
    left join sub_goods s on ig.id = s.goods_id
    left join procurement_return_item pi on ig.id = pi.goods_id 
    left join procurement_return p on pi.procurement_return_id = p.id 
    left join procurement_supplier ps on p.supplier_id = ps.id where ps.id = ?1 and g.id = ig.id and (s.id = sg.id || (s.id is null and sg.id is null)) and pi.sub_goods_id = s.id
    group by ig.id,s.id) as i on i.oid = pr.id where i.oid = pr.id and i.id = g.id and sg.id = pr.sub_goods_id)
,(select pi.purchase_price from procurement_item pi left join (select ig.id as id,s.id as sid,max(ipi.id ) as oid from goods as ig
left join sub_goods s on ig.id = s.goods_id
left join procurement_item ipi on ig.id = ipi.goods_id left join procurement p on p.id = ipi.procurement_id
left join procurement_supplier ps on p.supplier_id = ps.id where ps.id = ?1 and g.id = ig.id and (s.id = sg.id || (s.id is null and sg.id is null)) and ipi.sub_goods_id = s.id
group by ig.id,s.id) as i  on i.oid = pi.id where i.oid = pi.id and i.id = g.id and sg.id = pi.sub_goods_id)) as rememberedReturnPrice from goods g 
left join `supplier-system`.sub_goods sg on g.id = sg.goods_id 
""",
    name = "Goods.findAllWithRememberedProcurementReturnPrice",
    resultSetMapping = "goodsSummaryWithProcurementReturnItemPrice"
)
@SqlResultSetMapping(
    name = "goodsSummaryWithProcurementReturnItemPrice",
    entities = [EntityResult(
        entityClass = Goods::class,
    ), EntityResult(
        entityClass = SubGoods::class,
        fields = [
            FieldResult(
                name = "id",
                column = "sg_id",
            ),
            FieldResult(
                name = "number",
                column = "sg_number",
            ),
            FieldResult(
                name = "name",
                column = "sg_name",
            ),
            FieldResult(
                name = "amount",
                column = "sg_amount",
            )
        ]
    )],
    columns = [
        ColumnResult(
            name = "rememberedReturnPrice",
            type = BigDecimal::class
        )
    ]
)
class Goods {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    var id: Int = 0

    @Column(nullable = false)
    lateinit var name: String

    @Transient
    private var _amount = 0

    @get:ColumnDefault("0")
    var amount: Int
        get() {
            return if (this.subGoodsList.isEmpty()) {
                this._amount
            } else if (this.compositeGoodsList.isEmpty()) {
                this.compositeGoodsList.sumOf { it.amount }
            } else {
                this.subGoodsList.sumOf { it.amount }
            }
        }
        set(value) {
            this._amount = value
        }

    lateinit var barcode: String

    var price: BigDecimal = BigDecimal(0)

    @CreatedDate
    lateinit var createdDateTime: OffsetDateTime

    @LastModifiedDate
    var lastModifiedDateTime: OffsetDateTime? = null

    @ManyToOne
    @JoinColumn
    var category: GoodsCategory? = null

    @ManyToOne
    @JoinColumn
    var brand: GoodsBrand? = null

    @get:Transient
    val factory: Factory?
        get() = brand?.factory

    @ManyToOne(optional = false)
    @JoinColumn
    lateinit var systemService: SystemService

    /**
     * 厂家给的箱规
     */
    var boxSpecification: String? = null

    @OneToMany(mappedBy = "goods", fetch = FetchType.LAZY, cascade = [CascadeType.ALL])
    @OrderBy(value = "basicMultiple, id asc")
    var units: MutableList<GoodsUnit> = mutableListOf()

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "goods")
    var directSaleOrderItems: MutableList<DirectSaleOrderItem> = mutableListOf()

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "goods")
    var procurementItems: MutableList<ProcurementItem> = mutableListOf()

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "goods")
    var procurementReturnItems: MutableList<ProcurementReturnItem> = mutableListOf()

    var costPrice: BigDecimal? = null

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "goods")
    var markets: MutableList<MarketGoods> = mutableListOf()

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "goods", cascade = [CascadeType.ALL], orphanRemoval = true)
    var checkInventoryRecordItems: MutableList<CheckInventoryRecordItem> = mutableListOf()

    /**
     * 注册证编号
     */
    var registrationCertificateNumber: String? = null

    /**
     * 备案编号
     */
    var recordFilingNumber: String? = null

    /**
     * 保质期
     *
     * [Period.ZERO] 代表无限期
     * null 代表未填写
     */
    var expressionPeriod: Period? = null

    var note: String? = null

    /**
     * 执行标准
     */
    var executiveStandard: String? = null

    @OneToMany(mappedBy = "goods", cascade = [CascadeType.ALL])
    var subGoodsList: MutableList<SubGoods> = arrayListOf()

    @OneToMany(mappedBy = "sourceGoods", cascade = [CascadeType.ALL])
    var compositeGoodsList: MutableList<CompositeGoods> = arrayListOf()

    @ColumnDefault("0")
    @Column(nullable = false)
    var disable: Boolean = false

    override fun equals(other: Any?): Boolean {
        if (this === other) return true
        if (other !is Goods) return false

        if (id != other.id) return false

        return true
    }

    override fun hashCode(): Int {
        return id
    }

}

val Goods.basicUnit
    get() = this.units.find { it.isBasic }

val Goods.excelRow
    get() = GoodsRow(
        name,
        brand?.name,
        price,
        amount,
        boxSpecification,
        this.basicUnit?.name,
        barcode,
        category?.name,
        factory?.name,
        null,
        null,
        this.registrationCertificateNumber,
        this.recordFilingNumber,
        this.note,
        this.executiveStandard,
    )
